from docx import Document


def analyze_word(path):
    tables_list = []
    document = Document(path)
    tables = document.tables  # 获取文件中的表格集
    if not tables:
        return tables_list
    for table in tables:
        table_list = []
        rowCount = len(table.rows)
        columnCount = len(table.columns)
        # 遍历行。解析每一列
        for rowI in range(0, rowCount):
            items = []
            for columnI in range(0, columnCount):
                item = table.cell(rowI, columnI).text
                items.append(item)
            table_list.append(items)
        tables_list.append(table_list)
    return tables_list


def to_sql_list(tables_list):
    new_list = []
    for item in tables_list:
        if "基表" in item[0][0]:
            new_list.append(item)
    return new_list


sql_template = """
CREATE TABLE {en} (
{columns}

    PRIMARY KEY (`id`)
) COMMENT ='{cn}';
"""

sql_field_template = """    {en}        {type}          {not_null}          COMMENT '{comment}',\n"""


def printSql(sql_item):
    table_obj = {
        "表中文名": None,
        "表英文名": None,
        "columns": []

    }
    for item in sql_item:
        temp = item[0]
        re = "基表中文名:"
        if re in temp:
            table_obj["表中文名"] = temp.replace(re, '')
            continue
        re = "基表英文名:"
        if re in temp:
            table_obj["表英文名"] = temp.replace(re, '')
            continue
        if "说明" in temp or "外键" in temp or "索引" in temp or "建表语句" in temp or "序号" in temp:
            continue
        # 解析字段
        column_obj = {
            "字段英文名": item[1],
            "字段注释": item[2].replace("\n", ";"),
            "字段类型": item[3],
            "字段非空": "NOT NULL" if "是" == item[5] else "        ",
        }
        table_obj["columns"].append(column_obj)

    table_columns = table_obj["columns"]

    field_str = ''
    for field in table_columns:
        field_str += sql_field_template.format(en=field["字段英文名"], type=field["字段类型"], not_null=field["字段非空"],
                                               comment=field["字段注释"])
    sql_str = sql_template.format(en=table_obj["表英文名"], cn=table_obj["表中文名"], columns=field_str)

    return sql_str


if __name__ == '__main__':
    tables_list = analyze_word('./云创重构版数据库设计.docx')
    sql_list = to_sql_list(tables_list)

    str = ''
    for item in sql_list:
        str += printSql(item)

    f = open('./云创重构版.sql', 'w',encoding="utf-8")
    f.write(str)
    f.close()
